<?php
/**
 * GioCMS
 * 
 * LICENSE
 *
 * This source file is subject to the GNU GENERAL PUBLIC LICENSE Version 2 
 * that is bundled with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://www.gnu.org/licenses/gpl-2.0.txt
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@ninhgio.com so we can send you a copy immediately.
 * 
 * @copyright	Copyright (c) 2010-2011 GioCMS (http://cms.ninhgio.com)
 * @license		http://www.gnu.org/licenses/gpl-2.0.txt GNU GENERAL PUBLIC LICENSE Version 2
 * @author		NinhGio - ninhgio@gmail.com
 * @since		1.0
 */

class Modules_Page_Models_Mysql_Page extends Modules_Page_Models_Page
{
	protected $_table = 'page';
	
	public function getById($id)
	{
		$sql = sprintf("SELECT * FROM " . $this->_conn->_tablePrefix . $this->_table . " 
						WHERE page_id = '%s'
						LIMIT 1", 
						$this->_conn->escape($id));
						
		$rs  = $this->_conn->query($sql);
		$return = (0 == $this->_conn->count($rs)) ? null : $this->_conn->fetchAll($rs);
		$this->_conn->freeResult($rs);
		return $return;
	}
	
	public function getBySlug($slug, $pageId = null)
	{
		$sql = sprintf("SELECT * FROM " . $this->_conn->_tablePrefix . $this->_table . " 
						WHERE slug = '%s'", $this->_conn->escape($slug));
		if ($pageId) { 
			$sql .= ' AND page_id != "%s"';
			$sql = sprintf($sql, $this->_conn->escape($pageId));
		}
		$sql .= ' LIMIT 1';
		
		$rs  = $this->_conn->query($sql);
		$return = (0 == $this->_conn->count($rs)) ? null : $this->_conn->fetchAll($rs);
		$this->_conn->freeResult($rs);
		return $return;
	}
	
	public function add($page)
	{
		/**
		 * Calculate the right Id
		 */
		$parentId = (int)$page['parent_id'];
		if ($parentId) {
			$sql = sprintf('SELECT right_id FROM ' . $this->_conn->_tablePrefix . $this->_table . ' WHERE page_id = "%s" AND language = "%s"', $this->_conn->escape($parentId), $this->_conn->escape($page['language']));
		} else {
			$sql = sprintf('SELECT MAX(right_id) AS right_id FROM ' . $this->_conn->_tablePrefix . $this->_table . ' WHERE language = "%s"', $this->_conn->escape($page['language']));
		}
		$rs = $this->_conn->query($sql);
		$right   = (0 == $this->_conn->count($rs)) ? null : $this->_conn->fetchAll($rs);
		
		$rightId = ($parentId) ? $right['right_id'] : $right['right_id'] + 1;
		if ($rightId != null) {
			$sql = 'UPDATE ' . $this->_conn->_tablePrefix . $this->_table . '
					SET left_id = IF(left_id > ' . $this->_conn->escape($rightId) . ', left_id + 2, left_id), 
						right_id = IF(right_id >= ' . $this->_conn->escape($rightId) . ', right_id + 2, right_id)
					WHERE language = "' . $this->_conn->escape($page['language']) . '"';
			$this->_conn->query($sql);
							
			if (isset($page['page_id']) && $page['page_id'] != null) {
				$sql = sprintf("INSERT INTO " . $this->_conn->_tablePrefix . $this->_table . " (page_id, name, slug, description, content, created_date, modified_date,
									num_views, user_id, left_id, right_id, parent_id, language, status)
								VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s',
									'%s', '%s', '%s', '%s', '%s', '%s', '%s')",
								$this->_conn->escape($page['page_id']),
								$this->_conn->escape($page['name']),
								$this->_conn->escape($page['slug']),
								$this->_conn->escape($page['description']),
								$this->_conn->escape($page['content']),
								$this->_conn->escape($page['created_date']),
								$this->_conn->escape($page['modified_date']),
								$this->_conn->escape($page['num_views']),
								$this->_conn->escape($page['user_id']),
								$rightId,
								$rightId + 1,
								$this->_conn->escape($parentId),
								$this->_conn->escape($page['language']),
								$this->_conn->escape($page['status']));
				$this->_conn->query($sql);
				return $page['page_id'];	
			} else {
				$sql = sprintf("INSERT INTO " . $this->_conn->_tablePrefix . $this->_table . " (name, slug, description, content, created_date, modified_date,
								num_views, user_id, left_id, right_id, parent_id, language, status)
								VALUES ('%s', '%s', '%s', '%s', '%s', '%s',
									'%s', '%s', '%s', '%s', '%s', '%s', '%s')",
								$this->_conn->escape($page['name']),
								$this->_conn->escape($page['slug']),
								$this->_conn->escape($page['description']),
								$this->_conn->escape($page['content']),
								$this->_conn->escape($page['created_date']),
								$this->_conn->escape($page['modified_date']),
								$this->_conn->escape($page['num_views']),
								$this->_conn->escape($page['user_id']),
								$rightId,
								$rightId + 1,
								$this->_conn->escape($parentId),
								$this->_conn->escape($page['language']),
								$this->_conn->escape($page['status']));
				$this->_conn->query($sql);
				return $this->_conn->lastInsertId();
			}
		}
	}
	
	public function count($condition = array())
	{
		$sql = 'SELECT COUNT(*) as num_pages FROM ' . $this->_conn->_tablePrefix . $this->_table;
		$where = ' WHERE 1';
		if (isset($condition['parent_id'])) {
			$where .= ' AND parent_id = "' . $this->_conn->escape($condition['parent_id']) . '"';
		}
		$sql .= $where;
		$rs = $this->_conn->query($sql);
		$row = $this->_conn->fetchAll($rs);
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $row['num_pages'];
	}
	
	public function update($page)
	{
		$sql = sprintf("UPDATE " . $this->_conn->_tablePrefix . $this->_table . "
						SET name = '%s', slug = '%s', description = '%s', content = '%s', modified_date = '%s', language = '%s', status = '%s'
						WHERE page_id = '%s'",
						$this->_conn->escape($page['name']),
						$this->_conn->escape($page['slug']),
						$this->_conn->escape($page['description']),
						$this->_conn->escape($page['content']),
						$this->_conn->escape($page['modified_date']),
						$this->_conn->escape($page['language']),
						$this->_conn->escape($page['status']),
						$this->_conn->escape($page['page_id']));
		$this->_conn->query($sql);
	}
	
	public function updateOrder($page)
	{
		$sql = sprintf("UPDATE " . $this->_conn->_tablePrefix . $this->_table . "
						SET parent_id = '%s', left_id = '%s', right_id = '%s'
						WHERE page_id = '%s'",
						$this->_conn->escape($page['parent_id']),
						$this->_conn->escape($page['left_id']),
						$this->_conn->escape($page['right_id']),
						$this->_conn->escape($page['page_id']));
		$this->_conn->query($sql);
	}
	
	public function delete($page)
	{
		$sql = sprintf("DELETE FROM " . $this->_conn->_tablePrefix . $this->_table . "  
						WHERE page_id = '%s' AND language = '%s'",
						$this->_conn->escape($page['page_id']),
						$this->_conn->escape($page['language']));
		$this->_conn->query($sql);
		
		$sql = sprintf("UPDATE " . $this->_conn->_tablePrefix . $this->_table . "
						SET left_id = left_id - 1, right_id = right_id - 1
						WHERE language = '%s' AND left_id BETWEEN '%s' AND '%s'",
						$this->_conn->escape($page['language']),
						$this->_conn->escape($page['left_id']),
						$this->_conn->escape($page['right_id']));
		$this->_conn->query($sql);

		$sql = sprintf("UPDATE " . $this->_conn->_tablePrefix . $this->_table . " 
						SET right_id = right_id - 2
						WHERE right_id > '%s' AND language = '%s'",
						$this->_conn->escape($page['right_id']),
						$this->_conn->escape($page['language']));
		$this->_conn->query($sql);
		
		$sql = sprintf("UPDATE " . $this->_conn->_tablePrefix . $this->_table . "
						SET left_id = left_id - 2
						WHERE left_id > '%s' AND language = '%s'",
						$this->_conn->escape($page['right_id']),
						$this->_conn->escape($page['language']));
		$this->_conn->query($sql);
	}
	
	public function getTree()
	{
		$sql = sprintf("SELECT node.page_id, node.name, node.slug, (COUNT(parent.name) - 1) AS depth,
							node.left_id, node.right_id, node.parent_id, node.language, node.status 
						FROM " . $this->_conn->_tablePrefix . $this->_table . " AS node,
							" . $this->_conn->_tablePrefix . $this->_table . " AS parent
						WHERE node.left_id BETWEEN parent.left_id AND parent.right_id
							AND node.language = '%s'
							AND parent.language = '%s'
						GROUP BY node.page_id
						ORDER BY node.left_id",
						$this->_conn->escape($this->_lang),
						$this->_conn->escape($this->_lang));
		$rs   = $this->_conn->query($sql);
		$rows = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$rows[] = $row;
		}
		$this->_conn->freeResult($rs);
		return $rows;
	}
	

	public function getParents($pageId, $lang)
	{
		$sql = sprintf("SELECT parent.* 
						FROM " . $this->_conn->_tablePrefix . $this->_table . " AS node, " . $this->_conn->_tablePrefix . $this->_table . " AS parent
						WHERE node.left_id BETWEEN parent.left_id AND parent.right_id
							AND node.page_id = '%s' AND node.language = '%s'
							AND parent.language = '%s'
						ORDER BY parent.left_id",
						$this->_conn->escape($pageId),
						$this->_conn->escape($lang),
						$this->_conn->escape($lang));
		$rs   = $this->_conn->query($sql);
		$rows = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$rows[] = $row;
		}
		$this->_conn->freeResult($rs);
		return $rows;
	}
	
	public function updateParents($lang)
	{
		$sql = sprintf("SELECT node.page_id
						FROM " . $this->_conn->_tablePrefix . $this->_table . " AS node,
							" . $this->_conn->_tablePrefix . $this->_table . " AS parent
						WHERE node.left_id BETWEEN parent.left_id AND parent.right_id
							AND node.language = '%s'
							AND parent.language = '%s'
						GROUP BY node.page_id
						HAVING (COUNT(parent.name) - 1) = 0",
						$this->_conn->escape($lang),
						$this->_conn->escape($lang));
		$rs = $this->_conn->query($sql);
		$rows = array();
		while($row = $this->_conn->fetchAll($rs)) {
			$rows[] = $row['page_id'];
		}
		$this->_conn->freeResult($rs);
		$stringId = implode(',', $rows);
		$sql = sprintf("UPDATE " . $this->_conn->_tablePrefix . $this->_table . " SET parent_id = 0 WHERE page_id IN (%s)", $stringId);
		$this->_conn->query($sql);
		return $this->_conn->affectedRows();
	}
	
	/* ========== For translation =========================================== */
	
	public function getTranslations($item, $lang = null)
	{
		$sql = sprintf('SELECT p.* FROM ' . $this->_conn->_tablePrefix . $this->_table . ' AS p
						INNER JOIN 
						(
							SELECT tr1.* FROM ' . $this->_conn->_tablePrefix . 'core_translation AS tr1
							INNER JOIN ' . $this->_conn->_tablePrefix . 'core_translation AS tr2 
								ON (tr1.item_id = "%s" AND tr1.source_item_id = tr2.item_id) 
								OR (tr2.item_id = "%s" AND tr1.item_id = tr2.source_item_id)
								OR (tr1.source_item_id = "%s" AND tr1.source_item_id = tr2.source_item_id)
							WHERE tr1.item_class = "%s" AND tr2.item_class = "%s"
							GROUP by tr1.translation_id
						) AS tr
							ON tr.item_id = p.page_id',
						$this->_conn->escape($item['page_id']),
						$this->_conn->escape($item['page_id']),
						$this->_conn->escape($item['page_id']),
						'Modules_Page_Models_Page',
						'Modules_Page_Models_Page');
		
		$rs   = $this->_conn->query($sql);
		$rows = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$rows[] = $row;
		}
		$this->_conn->freeResult($rs);
		return $rows;
	}
	
	public function getTranslatable($lang)
	{
		$sql = sprintf('SELECT p.*, (tr.item_id IS NULL) AS translatable
						FROM
						(
							SELECT node.page_id, node.name, node.slug, (COUNT(parent.name) - 1) AS depth, 
								node.left_id, node.right_id, node.parent_id
							FROM ' . $this->_conn->_tablePrefix . $this->_table . ' AS node, 
								' . $this->_conn->_tablePrefix . $this->_table . ' AS parent
							WHERE node.left_id BETWEEN parent.left_id AND parent.right_id
								AND node.language = "%s"
								AND parent.language = "%s"
							GROUP BY node.page_id
							ORDER BY node.left_id
						) AS p
						LEFT JOIN ' . $this->_conn->_tablePrefix . 'core_translation AS tr
							ON tr.source_item_id = p.page_id
							AND tr.item_class = "%s"
							AND tr.source_language = "%s"
							AND tr.language = "%s"',
						$this->_conn->escape($this->_lang),
						$this->_conn->escape($this->_lang),
						'Modules_Page_Models_Page',
						$this->_conn->escape($this->getLang()),
						$this->_conn->escape($lang));
				//echo $sql; die();
		$rs   = $this->_conn->query($sql);
		$rows = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$rows[] = $row;
		}
		$this->_conn->freeResult($rs);
		return $rows;
	}
	
	public function getSource($page)
	{
		$sql = sprintf("SELECT p.* 
						FROM " . $this->_conn->_tablePrefix . $this->_table . " AS p
						INNER JOIN " . $this->_conn->_tablePrefix . "core_translation AS tr
							ON p.page_id = tr.source_item_id
						WHERE tr.item_id = '%s' AND tr.item_class = '%s'
						LIMIT 1",
						$this->_conn->escape($page['page_id']),
						'Modules_Page_Models_Page');
		
		$rs  = $this->_conn->query($sql);
		$return = (0 == $this->_conn->count($rs)) ? null : $this->_conn->fetchAll($rs);
		$this->_conn->freeResult($rs);
		return $return;
	}
}
